package projects;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.List;

@SuppressWarnings("serial")
public class project implements Serializable{
	private Integer projectId;
	private Integer clientId;
	private String name;
	private String description;
	private Date startDate;
	private Date endDate;
	private List<milestone> milestones;
	private Double effort;
	private String code;
	private Integer projectStateId;
	private String projectStateReason;
	private Double estimatedCost;
	private Double actualCost;
	
	public Double getEstimatedCost() {
		return estimatedCost;
	}

	public void setEstimatedCost(Double estimatedCost) {
		this.estimatedCost = estimatedCost;
	}

	public Double getActualCost() {
		return actualCost;
	}

	public void setActualCost(Double actualCost) {
		this.actualCost = actualCost;
	}

	public Integer getProjectStateId() {
		return projectStateId;
	}

	public void setProjectStateId(Integer projectStateId) {
		this.projectStateId = projectStateId;
	}

	public String getProjectStateReason() {
		return projectStateReason;
	}

	public void setProjectStateReason(String projectStateReason) {
		this.projectStateReason = projectStateReason;
	}
	
	public Integer getProjectId() {
		return projectId;
	}

	public void setProjectId(Integer projectId) {
		this.projectId = projectId;
	}

	public Integer getClientId() {
		return clientId;
	}

	public void setClientId(Integer clientId) {
		this.clientId = clientId;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getDescription() {
		return description;
	}

	public void setDescription(String description) {
		this.description = description;
	}

	public String getStartDate() {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd");
		return format.format(startDate);
	}

	public void setStartDate(String startDate) throws ParseException {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd");
		this.startDate = format.parse(startDate);
	}

	public String getEndDate() {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd");
		return format.format(endDate);
	}

	public void setEndDate(String endDate) throws ParseException {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd");
		this.endDate = format.parse(endDate);
	}

	public List<milestone> getMilestones() {
		return milestones;
	}

	public void setMilestones(List<milestone> milestones) {
		this.milestones = milestones;
	}

	public Double getEffort() {
		return effort;
	}

	public void setEffort(Double effort) {
		this.effort = effort;
	}

	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

//-------------------- Create a new Project(a new record into project's table)----------------------------
	
	public boolean createProject(){
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement insertProject = (PreparedStatement) con.prepareStatement("insert into projects(clientId, name, description, startDate, endDate, projectEffort, projectStateId, projectStateReason, estimatedCost) values(?, ?, ?, ?, ?, ?, ?, ?, ?)", PreparedStatement.RETURN_GENERATED_KEYS);
			insertProject.setInt(1, this.getClientId());
			insertProject.setString(2, this.getName());
			insertProject.setString(3, this.getDescription());
			insertProject.setString(4, this.getStartDate());
			insertProject.setString(5, this.getEndDate());
			insertProject.setDouble(6, this.getEffort());
			insertProject.setInt(7, this.getProjectStateId());
			insertProject.setString(8, this.getProjectStateReason());
			insertProject.setDouble(9, this.getEstimatedCost());
			Integer result = insertProject.executeUpdate();
			
			if (result == 1){
				ResultSet rs = insertProject.getGeneratedKeys();
				while (rs.next()){
					this.setProjectId(rs.getInt(1));
					this.insertClientCode(clientId, rs);
					this.assignTeamMember(rs);						// <--------------------> add's the employee null to the project for use in tasks
					if(rs.getInt(1) > 0) isExecuted = true;
				}
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}
	
//-------------------- Insert's the project's code into the project's table------------------------------- 	
	
	public boolean insertClientCode(Integer clientId, ResultSet rs){
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
					
			PreparedStatement getClientCode = (PreparedStatement) con.prepareStatement("select code from clients where clientId = ?");
			getClientCode.setInt(1, clientId);
			ResultSet RSgetClientCode = getClientCode.executeQuery();

			while (RSgetClientCode.next()){
				String projectCode = RSgetClientCode.getString(1)+"."+rs.getInt(1);
				PreparedStatement insertProjectCode = (PreparedStatement) con.prepareStatement("update projects set code = ? where projectId = ?");
				insertProjectCode.setString(1, projectCode);
				insertProjectCode.setInt(2, rs.getInt(1));
				insertProjectCode.executeUpdate();
			}
			isExecuted = true;
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}

//-------------------- Update's information related to a specific project --------------------------------	
	
	public boolean assignTeamMember(ResultSet rs){     
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement addTeamMember = (PreparedStatement) con.prepareStatement("insert into team(projectId, employeeId, roleId) values(?,1,1)");
			addTeamMember.setInt(1, rs.getInt(1));
			Integer result = addTeamMember.executeUpdate();
			
			if (result == 1){
				 isExecuted = true;
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}
	
//------------------------------------------------------------------------------------------------------------------------------------------	
	
	public boolean updateProject(){
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement updateProject = (PreparedStatement) con.prepareStatement("UPDATE projects SET name = ?, description = ?, startDate = ?,endDate = ?, projectEffort = ?, estimatedCost = ? WHERE projectId = ?");
			updateProject.setString(1, this.getName());
			updateProject.setString(2, this.getDescription());
			updateProject.setString(3, this.getStartDate());
			updateProject.setString(4, this.getEndDate());
			updateProject.setDouble(5, this.getEffort());
			updateProject.setDouble(6, this.getEstimatedCost());
			updateProject.setInt(7, this.getProjectId());
			Integer result = updateProject.executeUpdate();
			
			if (result == 1){
				 isExecuted = true;
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}
	
//------------------- Update's a project's State and stateReason -----------------------------------------	
	
	public boolean updateProjectState(){
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement updateProject = (PreparedStatement) con.prepareStatement("UPDATE projects SET projectStateId = ?, projectStateReason = ? WHERE projectId = ?");
			updateProject.setInt(1, this.getProjectStateId());
			updateProject.setString(2, this.getProjectStateReason());
			updateProject.setInt(3, this.getProjectId());
			Integer result = updateProject.executeUpdate();
			
			if (result == 1){
				 isExecuted = true;
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}
	
//------------------- Lists the project's that the current user is implicated in -------------------------	
	
	@SuppressWarnings("rawtypes")
	public Hashtable queryProjectsByLoggedUser(Integer employeeId){
		Connection con = null;
		Hashtable<Integer, ArrayList<String>> CNPI = new Hashtable<Integer, ArrayList<String>>();  							//CNPI - code-name-ptojectId
		
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement query = (PreparedStatement) con.prepareStatement("select prj.code, prj.name, datediff(prj.endDate, prj.startDate), ps.projectStateName, r.roleName, prj.projectId from projects prj, team t, projectStates ps, roles r where prj.projectId = t.projectId and prj.projectStateId = ps.projectStateId and t.roleId = r.roleId and t.employeeId = ?");
			query.setInt(1, employeeId);
			ResultSet rs = query.executeQuery();
			
			while(rs.next()){
				ArrayList<String> projectInfo = new ArrayList<String>();
				projectInfo.add(rs.getString(1));                         // 1. am adaugat in array list codul proiectului
				projectInfo.add(rs.getString(2));						  // 2. am adaugat in array list numele proiectului	
				projectInfo.add(rs.getString(3));						  // 3. am adaugat in array list numarul de zile ramase
				projectInfo.add(rs.getString(4));						  // 4. am adaugat in array list Statusul proiectului
				projectInfo.add(rs.getString(5));						  // 4. am adaugat in array list rolul angajatului in proiectul respectiv
				CNPI.put(rs.getInt(6), projectInfo);                      // 3. am adaugat in hashmap pe 1 pozitie projectId'ul si pe a 2'a ArrayList'ul 
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return CNPI;
	}
	
//------------------- Add a team member to a specific project --------------------------------------------
	
	public boolean assignTeamMember(Integer employeeId, Integer roleId){     
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement addTeamMember = (PreparedStatement) con.prepareStatement("insert into team(projectId, employeeId, roleId) values(?,?,?)");
			addTeamMember.setInt(1, this.getProjectId());
			addTeamMember.setInt(2, employeeId);
			addTeamMember.setInt(3, roleId);
			Integer result = addTeamMember.executeUpdate();
			
			if (result == 1){
				 isExecuted = true;
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}
	
//------------------- Searches for a employee by his full name--------------------------------------------
	
	public Hashtable<Integer, String> queryEmployees(String fullName){
		Connection con = null;
		Hashtable<Integer, String> matches = new Hashtable<Integer, String>();
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/licenseDb","root", "root");
			
			PreparedStatement queryEmployee = (PreparedStatement) con.prepareStatement("select customerId, concat(firstName,' ',lastName) from Customer where lower(firstName) like ? or lower(lastName) like ?");
			queryEmployee.setString(1, "%" + fullName + "%");
			queryEmployee.setString(2, "%" + fullName + "%");
			ResultSet rs = queryEmployee.executeQuery();
			
			
			while (rs.next()){
				 matches.put(rs.getInt(1), rs.getString(2));
			}
			return matches;
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return matches;
	}
	
//------------------- Searches for a project by it's designated code -------------------------------------	
	
	public ArrayList<String> queryProjectsByCode(String projectCode){     
		Connection con = null;
		ArrayList<String> info = new ArrayList<String>();
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889","root", "root");
			
			PreparedStatement addTeamMember = (PreparedStatement) con.prepareStatement("select prj.projectId ,prj.name, concat(empl.lastName,' ',empl.firstName) from projectsDb.projects prj, projectsDb.team t, licenseDb.Customer empl where t.projectId = prj.projectId and empl.customerId = t.employeeId and roleId = 3 and lower(prj.code) = ?");
			addTeamMember.setString(1, projectCode);
			ResultSet rs = addTeamMember.executeQuery();
			rs.last();
			
			if (rs.getRow() == 1){
				 info.add(rs.getString(1));      //--> projectId
				 info.add(rs.getString(2));      //--> project name
				 info.add(rs.getString(3));      //--> project manager name
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return info;
	}

//------------------- Searches for a project by it's designated projectId --------------------------------	
	
	public ArrayList<String> queryProjectsById(Integer projectId){     
		Connection con = null;
		ArrayList<String> projectInfo = new ArrayList<String>();
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement addTeamMember = (PreparedStatement) con.prepareStatement("select clientId, name, description, startDate, endDate, code, projectEffort from projects where projectId = ?");
			addTeamMember.setInt(1, projectId);
			ResultSet rs = addTeamMember.executeQuery();
			rs.last();
			
			if (rs.getRow() == 1){
				 projectInfo.add(rs.getString(1));      //--> clientId
				 projectInfo.add(rs.getString(2));      //--> project name
				 projectInfo.add(rs.getString(3));      //--> project description
				 projectInfo.add(rs.getString(4));      //--> project's begin date
				 projectInfo.add(rs.getString(5));      //--> project's end date
				 projectInfo.add(rs.getString(6));      //--> project's code
				 projectInfo.add(rs.getString(7));      //--> project's effort
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return projectInfo;
	}

//------------------- Removes a team member from a specific project --------------------------------------	

	public boolean removeTeamMember(Integer employeeId){
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement updateProject = (PreparedStatement) con.prepareStatement("delete from team where projectId = ? and employeeId = ?");
			updateProject.setInt(1, this.getProjectId());
			updateProject.setInt(2, employeeId);
			Integer result = updateProject.executeUpdate();
			
			if (result == 1){
				 isExecuted = true;
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}
	
	public Double projectCoplition(){
		Connection con = null;
		Double percentage = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement queryTotalTasks = (PreparedStatement) con.prepareStatement("select count(*) from tasks t, milestones m, projects p where p.projectId = m.projectId and m.milestoneId = t.milestoneId and p.projectId = ?");
			queryTotalTasks.setInt(1, this.getProjectId());
			ResultSet rstt = queryTotalTasks.executeQuery();
			rstt.last();
			
			PreparedStatement queryCompletedTasks = (PreparedStatement) con.prepareStatement("select count(*) from tasks t, milestones m, projects p, statusResolutions sr where p.projectId = m.projectId and m.milestoneId = t.milestoneId and t.taskId = sr.taskId and p.projectId = ? and sr.statusId = 4");
			queryCompletedTasks.setInt(1, this.getProjectId());
			ResultSet rsct = queryCompletedTasks.executeQuery();
			rsct.last();
			
			if (rstt.getRow() == 1 && rsct.getRow() == 1){
				 Integer totalTasks = rstt.getInt(1);
				 Integer completedTasks = rsct.getInt(1);
				 percentage = (double) ((completedTasks*100)/totalTasks);
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return percentage;
	}
	
	public Hashtable<Integer, ArrayList<String>> returnNumberOfTasks(){
		Connection con = null;
		Hashtable<Integer, ArrayList<String>> info = new Hashtable<Integer, ArrayList<String>>();
		
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement query = (PreparedStatement) con.prepareStatement("select distinct(ta.taskId),ta.taskName, sr.creationDate, datediff(ta.startDate, sysdate()), sr.statusId from tasks ta, statusResolutions sr where milestoneId in (select distinct(milestoneId) from milestones where projectId = ?) and ta.taskId = sr.taskId order by sr.taskId, sr.creationDate desc");
			query.setInt(1, this.getProjectId());
			ResultSet rs = query.executeQuery();
			Integer taskId = 0; 
			while (rs.next()){
				if (taskId == 0){
					taskId = rs.getInt(1);
					ArrayList<String> taskInfo = new ArrayList<String>();
					taskInfo.add(rs.getString(2));                         // 1. am adaugat in array list numele taskului
					taskInfo.add(rs.getString(3));						   // 2. am adaugat in array list data crearii taskului	
					taskInfo.add(rs.getString(4));						   // 3. am adaugat in array list numarul de zile ramase / overdue
					taskInfo.add(rs.getString(5));						   // 4. am adaugat in array list statusId-ul taskului
					info.put(rs.getInt(1), taskInfo);                      // 3. am adaugat in hashmap pe 1 pozitie taskId'ul si pe a 2'a ArrayList'ul 
				}
				else if (taskId == rs.getInt(1) ) {
					taskId = rs.getInt(1);
				}
				else{
					taskId = rs.getInt(1);
					ArrayList<String> taskInfo = new ArrayList<String>();
					taskInfo.add(rs.getString(2));                         // 1. am adaugat in array list numele taskului
					taskInfo.add(rs.getString(3));						   // 2. am adaugat in array list data crearii taskului	
					taskInfo.add(rs.getString(4));						   // 3. am adaugat in array list numarul de zile ramase / overdue
					taskInfo.add(rs.getString(5));						   // 4. am adaugat in array list statusId-ul taskului
					info.put(rs.getInt(1), taskInfo);                      // 3. am adaugat in hashmap pe 1 pozitie taskId'ul si pe a 2'a ArrayList'ul 
				}
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return info;
	}
	
	@SuppressWarnings("rawtypes")
	public ArrayList queryProjectInfo(){
		Connection con = null;
		ArrayList<String> info = new ArrayList<String>();  							//CNPI - code-name-ptojectId
		
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement query = (PreparedStatement) con.prepareStatement("select p.projectId, p.name, p.description, p.startDate, p.endDate, p.code, p.projectEffort, ps.projectStateName, p.projectStateReason, p.estimatedCost, c.name from projects p, projectStates ps, clients c where p.projectStateId = ps.projectStateId and p.clientId = c.clientId and projectId = ?");
			query.setInt(1, this.getProjectId());
			ResultSet rs = query.executeQuery();
			
			while(rs.next()){
				info.add(rs.getString(1));						  //    am adaugat in pe 1 pozitie  projectId Id'ul
				info.add(rs.getString(2));                        // 1. am adaugat in array list project name
				info.add(rs.getString(3));						  // 2. am adaugat in array list project description	
				info.add(rs.getString(4));						  // 3. am adaugat in array list startDate 
				info.add(rs.getString(5));						  // 3. am adaugat in array list endDate
				info.add(rs.getString(6));						  // 3. am adaugat in array list projectCode
				info.add(rs.getString(7));						  // 3. am adaugat in array list project Effort
				info.add(rs.getString(8));						  // 3. am adaugat in array list project State name
				info.add(rs.getString(9));						  // 3. am adaugat in array list project State reason
				info.add(rs.getString(10));						  // 3. am adaugat in array list project estimated cost
				info.add(rs.getString(11));						  // 3. am adaugat in array list client
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return info;
	}
	
	@SuppressWarnings("rawtypes")
	public Hashtable queryProjectTeam(){
		Connection con = null;
		Hashtable<Integer, ArrayList<String>> team = new Hashtable<Integer, ArrayList<String>>();  							//CNPI - code-name-ptojectId
		
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement query = (PreparedStatement) con.prepareStatement("select c.customerId, concat(c.firstName,' ',c.lastName), r.roleName from team t, licenseDb.Customer c, roles r where c.customerId = t.employeeId and r.roleId = t.roleId and t.projectId = ?");
			query.setInt(1, this.getProjectId());
			ResultSet rs = query.executeQuery();
			
			while(rs.next()){
				ArrayList<String> projectInfo = new ArrayList<String>();
				projectInfo.add(rs.getString(2));                         // 1. am adaugat in array list employee name
				projectInfo.add(rs.getString(3));						  // 2. am adaugat in array list role in project
				team.put(rs.getInt(1), projectInfo);                      // 3. am adaugat in hashmap pe 1 pozitie employeeId'ul si pe a 2'a ArrayList'ul 
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return team;
	}
	
	@SuppressWarnings("rawtypes")
	public Hashtable getActivityStream(Integer employeeId){
		Connection con = null;
		Hashtable<Integer, ArrayList<String>> team = new Hashtable<Integer, ArrayList<String>>();  							//CNPI - code-name-ptojectId
		@SuppressWarnings("unchecked")
		Enumeration<Integer> projectIds = this.queryProjectsByLoggedUser(employeeId).keys();
		ArrayList<Integer> projects = new ArrayList<Integer>();
		while(projectIds.hasMoreElements()){
			projects.add(projectIds.nextElement());
		}
		String ids = "";
		for(Integer project : projects){
			ids = ids + project + ",";
		}
		ids = ids.substring(0, ids.length()-1);
		//System.out.println(ids);
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement query = (PreparedStatement) con.prepareStatement("(select t.taskId, concat(c.firstName,' ',c.lastName), t.taskCreationDate as creationDate, p.code, p.projectId, '1'" + 
				" from tasks t, milestones m, licenseDb.Customer c, projects p" +
				" where m.milestoneId = t.milestoneId" +
				" and t.createdBy = c.customerId" +
				" and p.projectId = m.projectId" +
				" and m.projectId in (" + ids + ") order by creationDate desc limit 5)" +
				" union" + 
				" (select t.taskId, concat(c.firstName,' ',c.lastName), i.creationDate, p.code, p.projectId, '2'" +
						" from issues i, tasks t, milestones m, projects p, licenseDb.Customer c" + 
						" where i.taskId = t.taskId" +
						" and m.milestoneId = t.milestoneId" +
						" and m.projectId = p.projectId" +
						" and i.createdBy = c.customerId" +
						" and p.projectId in (" + ids + ") order by creationDate desc limit 5)" +
						" union" +
						" (select sr.taskId, concat(c.firstName,' ',c.lastName), sr.creationDate, p.code, p.projectId, '3'" +
						" from statusResolutions sr, licenseDb.Customer c, tasks t, milestones m, projects p" +
						" where sr.taskId = t.taskId" +
						" and c.customerId = sr.fromEmployeeId" +
						" and m.milestoneId = t.milestoneId" +
						" and p.projectId = m.projectId" +
						" and p.projectId in (" + ids + ")" +
						" and sr.fromEmployeeId !=1 order by creationDate desc limit 5)" +
						"order by creationDate desc");
			
			ResultSet rs = query.executeQuery();
			Integer i = 0;
			while(rs.next()){
				i = i+1;
				ArrayList<String> entry = new ArrayList<String>();
				//entry.add(i.toString());
				entry.add(rs.getString(1));//taskId
				entry.add(rs.getString(2));//concat
				entry.add(rs.getString(3));//creationDate
				entry.add(rs.getString(4));//code
				entry.add(rs.getString(5));//projectId
				entry.add(rs.getString(6));//type
				
				team.put(i, entry);                      // 3. am adaugat in hashmap pe 1 pozitie employeeId'ul si pe a 2'a ArrayList'ul 
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return team;
	}
	
	public static void main(String args[]) throws ParseException{
		project x = new project();
//		x.setClientId(1);
//		x.setName("ProgectName1 test");
//		x.setDescription("ProgectDescription1 test");
//		x.setStartDate("2011-01-31");
//		x.setEndDate("2011-03-25");
//		x.setEffort(55.0);
//		x.setProjectStateId(1);
//		x.setProjectStateReason("ProjectStateReason5 test");
//		x.setEstimatedCost(45.3);
//		x.createProject();
//		x.setProjectId(3);
//		System.out.println(x.updateProject());
		//x.setProjectId(3);
		//System.out.println(x.assignTeamMember(4, 5));
		//System.out.println(x.getActivityStream(3));
	}
	
	
}
